﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace LunarExcelAddIn
{
    public partial class MailDialog : Form
    {
        public MailDialog()
        {
            InitializeComponent();
        }

        private void cmbDataSourceSheet_DropDown(object sender, EventArgs e)
        {
            cmbDataSourceSheet.Items.Clear();

            foreach (Excel.Worksheet displayWorksheet in Globals.ThisAddIn.Application.Worksheets)
            {
                cmbDataSourceSheet.Items.Add(displayWorksheet.Name);
            }
        }

        private void cmbTemplateSheet_DropDown(object sender, EventArgs e)
        {
            cmbTemplateSheet.Items.Clear();

            foreach (Excel.Worksheet displayWorksheet in Globals.ThisAddIn.Application.Worksheets)
            {
                cmbTemplateSheet.Items.Add(displayWorksheet.Name);
            }
        }

        private void cmbDataSourceSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadData();

            cmbSheetNameField.Items.Clear();

            if (cmbDataSourceSheet.SelectedIndex >= 0)
            {
                Excel.Worksheet sheetDataSource = Globals.ThisAddIn.Application.Worksheets[cmbDataSourceSheet.SelectedItem as string] as Excel.Worksheet;

                if (sheetDataSource != null)
                {
                    var usedrange = sheetDataSource.UsedRange;
                    if (usedrange != null && usedrange.Rows.Count > 0)
                    {
                        var rowFst = Globals.ThisAddIn.Application.Intersect(usedrange, (usedrange[1, 1] as Excel.Range).EntireRow);
                        foreach (Excel.Range rng in rowFst)
                        {
                            if (rng != null && rng.Value != null && rng.Value.ToString() != "")
                            {
                                cmbSheetNameField.Items.Add(rng.Value.ToString());
                            }
                        }
                    }
                }
            }
        }

        private void cmbTemplateSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            LoadData();
        }

        /// <summary>
        /// 载入数据填充到模板中。
        /// </summary>
        private bool LoadData(int rowIndex = 1)
        {
            if (cmbDataSourceSheet.SelectedIndex < 0)
            {
                //太啰嗦
                //MessageBox.Show("请先指定用作模板的工作表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (cmbTemplateSheet.SelectedIndex < 0)
            {
                //太啰嗦
                //MessageBox.Show("请先指定用作数据源的工作表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (cmbTemplateSheet.SelectedItem.ToString() == cmbDataSourceSheet.SelectedItem.ToString())
            {
                MessageBox.Show("数据源表和模板表不能相同！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            Excel.Worksheet sheetTemplate = Globals.ThisAddIn.Application.Worksheets[cmbTemplateSheet.SelectedItem as string] as Excel.Worksheet;
            Excel.Worksheet sheetDataSource = Globals.ThisAddIn.Application.Worksheets[cmbDataSourceSheet.SelectedItem as string] as Excel.Worksheet;

            var usedRange = sheetDataSource.UsedRange;

            if (usedRange.Rows.Count <= 1 || usedRange.Columns.Count <= 0)
            {
                MessageBox.Show("数据源表中没有可用数据！\r\n\r\n注意：数据源表中第一行应是字段名；这些字段名应在模板中用于对单元格进行命名。", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (rowIndex < 1 || rowIndex >= usedRange.Rows.Count)
            {
                MessageBox.Show("行索引越界！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            numericUpDown1.Value = rowIndex;
            var rowFst = Globals.ThisAddIn.Application.Intersect(usedRange, (usedRange[1, 1] as Excel.Range).EntireRow);
            var row = Globals.ThisAddIn.Application.Intersect(usedRange, (usedRange[rowIndex + 1, 1] as Excel.Range).EntireRow);

            int i = 1;
            int validateFieldIndex = 1;
            int blank_count = 0;
            foreach (Excel.Range rng in rowFst)
            {
                if (rng.Value == null) { i++; continue; }
                if (FindName(rng.Value.ToString()) == false) { i++; continue; }
                Excel.Range destRng = sheetTemplate.Range[rng.Value.ToString()];
                if (destRng != null)
                {
                    if (i <= row.Count)
                    {

                        destRng.Value = (usedRange[rowIndex + 1, i] as Excel.Range).Value;
                        if (destRng.Value == null || string.IsNullOrEmpty(destRng.Value.ToString()))
                        {
                            blank_count++;
                        }
                    }
                    else
                    {
                        destRng.Value = "";
                        blank_count++;
                    }
                }
                i++;
                validateFieldIndex++;
            }

            if (blank_count >= validateFieldIndex - 1)
            {
                MessageBox.Show("此行记录无可填充内容！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            return true;
        }

        private bool LoadDataFromWorkBook(Excel.Workbook srcWorkbook, int rowIndex = 1)
        {
            if (srcWorkbook == null) return false;

            if (cmbDataSourceSheet.SelectedIndex < 0)
            {
                //太啰嗦
                //MessageBox.Show("请先指定用作模板的工作表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (cmbTemplateSheet.SelectedIndex < 0)
            {
                //太啰嗦
                //MessageBox.Show("请先指定用作数据源的工作表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (cmbTemplateSheet.SelectedItem.ToString() == cmbDataSourceSheet.SelectedItem.ToString())
            {
                MessageBox.Show("数据源表和模板表不能相同！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            Excel.Worksheet sheetTemplate = srcWorkbook.Worksheets[cmbTemplateSheet.SelectedItem as string] as Excel.Worksheet;
            Excel.Worksheet sheetDataSource = srcWorkbook.Worksheets[cmbDataSourceSheet.SelectedItem as string] as Excel.Worksheet;

            var usedRange = sheetDataSource.UsedRange;

            if (usedRange.Rows.Count <= 1 || usedRange.Columns.Count <= 0)
            {
                MessageBox.Show("数据源表中没有可用数据！\r\n\r\n注意：数据源表中第一行应是字段名；这些字段名应在模板中用于对单元格进行命名。", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            if (rowIndex < 1 || rowIndex >= usedRange.Rows.Count)
            {
                // MessageBox.Show("行索引越界！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            var rowFst = Globals.ThisAddIn.Application.Intersect(usedRange, (usedRange[1, 1] as Excel.Range).EntireRow);
            var row = Globals.ThisAddIn.Application.Intersect(usedRange, (usedRange[rowIndex + 1, 1] as Excel.Range).EntireRow);

            int i = 1;
            int validateFieldIndex = 1;
            int blank_count = 0;
            foreach (Excel.Range rng in rowFst)
            {
                if (rng.Value == null) { i++; continue; }
                if (FindNameFromWorkbook(srcWorkbook, rng.Value.ToString()) == false) { i++; continue; }

                Excel.Range destRng = sheetTemplate.Range[rng.Value.ToString()];
                if (destRng != null)
                {
                    if (i <= row.Count)
                    {
                        destRng.Value = (usedRange[rowIndex + 1, i] as Excel.Range).Value;
                        if (destRng.Value == null || string.IsNullOrEmpty(destRng.Value.ToString()))
                        {
                            blank_count++;
                        }
                    }
                    else
                    {
                        destRng.Value = "";
                        blank_count++;
                    }
                }
                i++;
                validateFieldIndex++;
            }

            if (blank_count >= validateFieldIndex - 1)
            {
                //MessageBox.Show("此行记录无可填充内容！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);              return false;  // 此处与 LoadData() 不同。
            }
            return true;
        }


        private bool FindName(string destname)
        {
            foreach (Excel.Name name in Globals.ThisAddIn.Application.ActiveWorkbook.Names)
            {
                if (name.Name == destname) return true;
            }
            return false;
        }

        private bool FindNameFromWorkbook(Excel.Workbook srcWorkbook, string destname)
        {
            if (srcWorkbook == null) return false;

            foreach (Excel.Name name in srcWorkbook.Names)
            {
                if (name.Name == destname) return true;
            }
            return false;
        }

        private void btnFstRecord_Click(object sender, EventArgs e)
        {
            var oldNum = numericUpDown1.Value;
            numericUpDown1.Value = 1;

            if (LoadData(1) == false)
            {
                numericUpDown1.Value = oldNum;
            }
        }

        private void btnPreviewRecord_Click(object sender, EventArgs e)
        {
            numericUpDown1.Value--;

            if (LoadData((int)numericUpDown1.Value) == false)
            {
                numericUpDown1.Value++;
            }
        }

        private void btnNextRecord_Click(object sender, EventArgs e)
        {
            numericUpDown1.Value++;

            if (LoadData((int)numericUpDown1.Value) == false)
            {
                numericUpDown1.Value--;
            }
        }

        private void btnLastRecord_Click(object sender, EventArgs e)
        {
            var oldNum = numericUpDown1.Value;
            numericUpDown1.Value = numericUpDown1.Maximum;

            if (LoadData((int)numericUpDown1.Value) == false)
            {
                numericUpDown1.Value = oldNum;
            }
        }

        private void btnPrintTemplate_Click(object sender, EventArgs e)
        {
            if (cmbTemplateSheet.SelectedIndex < 0) return;

            Excel.Worksheet sheetTemplate = Globals.ThisAddIn.Application.Worksheets[cmbTemplateSheet.SelectedItem as string] as Excel.Worksheet;
            if (sheetTemplate == null) return;

            sheetTemplate.PrintPreview();
        }

        private void numericUpDown1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                LoadData((int)numericUpDown1.Value);
            }
        }

        private void MailDialog_FormClosing(object sender, FormClosingEventArgs e)
        {
            e.Cancel = true;
            this.Hide();
        }

        private void btnSaveRecordAsFile_Click(object sender, EventArgs e)
        {
            if (cmbDataSourceSheet.SelectedIndex < 0)
            {
                MessageBox.Show("没找到数据源表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            if (cmbTemplateSheet.SelectedIndex < 0)
            {
                MessageBox.Show("没找到填充模板表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            Excel.Worksheet sheetTemplate = Globals.ThisAddIn.Application.Worksheets[cmbTemplateSheet.SelectedItem as string] as Excel.Worksheet;
            if (sheetTemplate == null)
            {
                MessageBox.Show("没找到填充模板表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            Excel.Worksheet sheetDataSource = Globals.ThisAddIn.Application.Worksheets[cmbDataSourceSheet.SelectedItem as string] as Excel.Worksheet;
            if (sheetDataSource == null)
            {
                MessageBox.Show("没找到数据源表！", "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            SaveFileDialog sfd = new SaveFileDialog()
            {
                Filter = "Excel 工作簿(*.xlsx)|*.xlsx",
            };

            var oldFileName = Globals.ThisAddIn.Application.ActiveWorkbook.FullName;
            if (System.IO.File.Exists(oldFileName))
            {
                var fi = new FileInfo(oldFileName);
                sfd.InitialDirectory = fi.Directory.FullName;
                sfd.FileName = fi.Name + "－导出文件.xlsx";
            }
            else
            {
                sfd.FileName = "LunarExcel-AddIn-OutputFile.xlsx";
            }

            if (sfd.ShowDialog() != DialogResult.OK)
            {
                return;
            }

            try
            {
                var usedrange = sheetDataSource.UsedRange;

                var srcWorkBook = Globals.ThisAddIn.Application.ActiveWorkbook;
                var newWorkBook = Globals.ThisAddIn.Application.Workbooks.Add();
                newWorkBook.SaveAs(sfd.FileName);

                if (ckxSaveRecordToNewSheet.Checked)
                {
                    //Globals.ThisAddIn.Application.ScreenUpdating = false;  //不去除这行无法更新数据。
                    for (int i = 1; i <= usedrange.Rows.Count; i++)
                    {
                        if (LoadDataFromWorkBook(srcWorkBook, i))
                        {
                            string fieldValue = FindFieldValueFromWorkbook(srcWorkBook, i);
                            Excel.Worksheet newSheet;
                            if (i <= 1)
                            {
                                newSheet = newWorkBook.Sheets[1];
                            }
                            else
                            {
                                newSheet = newWorkBook.Sheets.Add();
                            }

                            if (string.IsNullOrWhiteSpace(fieldValue) == false)
                            {
                                newSheet.Name = i.ToString() + "_" + fieldValue;
                            }
                            sheetTemplate.UsedRange.Copy(newSheet.Range["a1"]);
                        }
                    }
                    //Globals.ThisAddIn.Application.ScreenUpdating = true;
                }
                else
                {
                    int columnCount = (int)numColumns.Value;
                    int rowCount = 1;
                    var usedRange = sheetTemplate.UsedRange;
                    for (int i = 1; i <= usedrange.Rows.Count; i++)
                    {
                        if (LoadDataFromWorkBook(srcWorkBook, i))
                        {
                            string fieldValue = FindFieldValueFromWorkbook(srcWorkBook, i);
                            Excel.Worksheet destSheet = newWorkBook.Sheets[1];

                            var location = GetLocationCellMark(i, columnCount, usedRange);
                            usedRange.Copy(destSheet.Range[location]);
                            rowCount += sheetTemplate.UsedRange.Rows.Count + 1;  // 加个空行分割不同记录。
                        }
                    }
                }
                newWorkBook.Save();
                newWorkBook.Close();

                var answer = MessageBox.Show("导出成功！要打开吗？", "LunarExcel-AddIn", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (answer == DialogResult.Yes)
                {
                    System.Diagnostics.Process.Start("explorer.exe", (sfd.FileName.Contains(" ") ? ("\"" + sfd.FileName + "\"") : sfd.FileName));
                    LoadData((int)numericUpDown1.Value);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("保存出错！异常消息如下：\r\n" + ex.Message, "LunarExcel-AddIn", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
        }

        private string GetLocationCellMark(int i, int columnCount, Range usedRange)
        {
            var usedColCount = usedRange.Columns.Count;
            var usedRowCount = usedRange.Rows.Count;

            int rowIndex;
            int colIndex;
            int tmp = i % columnCount;
            if (tmp == 0)
            {
                rowIndex = (usedRowCount + 1) * ((int)(i / columnCount) - 1) + 1;
                colIndex = (usedColCount + 1) * (columnCount - 1) + 1;
            }
            else
            {
                rowIndex = (usedRowCount + 1) * ((int)(i / columnCount)) + 1;
                colIndex = (usedColCount + 1) * (tmp - 1) + 1;
            }

            return $"{ConvertToAlpha(colIndex)}{rowIndex}";
        }

        private object ConvertToAlpha(int colIndex)
        {
            if (colIndex >= 1 && colIndex <= 26)
            {
                var acode = (int)'a';
                var bcode = acode + colIndex - 1;
                var result_char = (char)bcode;
                return $"{result_char}";
            }

            return "a";
        }

        /// <summary>
        /// [备用]此方法抄自：
        /// https://www.evget.com/article/2009/4/24/10620.html
        /// </summary>
        /// <param name="app"></param>
        /// <param name="pasteRange"></param>
        /// <param name="beginCell"></param>
        /// <param name="fileName"></param>
        public static void CreatAndPaste(Excel.Application app, string pasteRange, string beginCell, string fileName)
        {
            //使用新的Excel进程添加一个新的工作簿
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Type.Missing);
            Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

            //粘贴
            ws.get_Range(beginCell, pasteRange).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
                Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
            ws.get_Range(beginCell, pasteRange).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats,
                Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
            ws.get_Range(beginCell, pasteRange).Columns.AutoFit();

            //保存
            wb.SaveCopyAs(fileName);

            //关闭工作簿
            Clipboard.Clear();
            wb.Close(false, Type.Missing, Type.Missing);

        }

        private string FindFieldValue(decimal ri)
        {
            if (cmbDataSourceSheet.SelectedIndex < 0) return "";
            if (cmbSheetNameField.SelectedIndex < 0) return "";

            var rowIndex = (int)ri;
            var fieldName = cmbSheetNameField.SelectedItem.ToString();

            Excel.Worksheet sheetDataSource = Globals.ThisAddIn.Application.Worksheets[cmbDataSourceSheet.SelectedItem as string] as Excel.Worksheet;

            var userRange = sheetDataSource.UsedRange;

            if (userRange.Rows.Count <= 1 || userRange.Columns.Count <= 0) return "";
            var rowFst = Globals.ThisAddIn.Application.Intersect(userRange, (userRange[1, 1] as Excel.Range).EntireRow);
            var row = Globals.ThisAddIn.Application.Intersect(userRange, (userRange[rowIndex + 1, 1] as Excel.Range).EntireRow);

            if (rowFst != null && row != null)
            {
                var ci = -1;
                for (int j = 1; j <= rowFst.Count; j++)
                {
                    Excel.Range cell = rowFst[j];
                    if (cell != null && cell.Value != null && cell.Value == fieldName)
                    {
                        ci = j;
                        break;
                    }
                }

                if (ci >= 0)
                {
                    Excel.Range result = row[ci];
                    return result.Value == null ? "" : result.Value.ToString();
                }
                else return "";
            }
            else return "";
        }
        private string FindFieldValueFromWorkbook(Excel.Workbook srcWorkbook, decimal ri)
        {
            if (srcWorkbook == null) return "";
            if (cmbDataSourceSheet.SelectedIndex < 0) return "";
            if (cmbSheetNameField.SelectedIndex < 0) return "";

            var rowIndex = (int)ri;
            var fieldName = cmbSheetNameField.SelectedItem.ToString();

            Excel.Worksheet sheetDataSource = srcWorkbook.Worksheets[cmbDataSourceSheet.SelectedItem as string] as Excel.Worksheet;

            var userRange = sheetDataSource.UsedRange;

            if (userRange.Rows.Count <= 1 || userRange.Columns.Count <= 0) return "";
            var rowFst = Globals.ThisAddIn.Application.Intersect(userRange, (userRange[1, 1] as Excel.Range).EntireRow);
            var row = Globals.ThisAddIn.Application.Intersect(userRange, (userRange[rowIndex + 1, 1] as Excel.Range).EntireRow);

            if (rowFst != null && row != null)
            {
                var ci = -1;
                for (int j = 1; j <= rowFst.Count; j++)
                {
                    Excel.Range cell = rowFst[j];
                    if (cell != null && cell.Value != null && cell.Value == fieldName)
                    {
                        ci = j;
                        break;
                    }
                }

                if (ci >= 0)
                {
                    Excel.Range result = row[ci];
                    return result.Value == null ? "" : result.Value.ToString();
                }
                else return "";
            }
            else return "";
        }

        private void ckxSaveRecordToNewSheet_CheckedChanged(object sender, EventArgs e)
        {
            if (ckxSaveRecordToNewSheet.Checked == true)
            {
                gpSheetNameField.Enabled =
                    cmbSheetNameField.Enabled = true;
                gbColumns.Enabled = false;
            }
            else
            {
                gpSheetNameField.Enabled =
                    cmbSheetNameField.Enabled = false;
                gbColumns.Enabled = true;
            }
        }
    }
}
